﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using DHQGHN.DAL.Common;
using DHQGHN.DAL.DataMapper;


namespace DHQGHN.DAL.Base
{
    public class SqlDAO
    {
        public const String Param_Action = "@Action";

        #region Property
        private readonly SqlConnection _connection;
        private System.Type _mapperType;

        public System.Type MapperType
        {
            get { return _mapperType; }
            set { _mapperType = value; }
        }

        private readonly IConnectionFactory _connectionFactory;
        private readonly ReaderWriterLockSlim _rwLock = new ReaderWriterLockSlim();
        private readonly LinkedList<AdoNetUnitOfWork> _uows = new LinkedList<AdoNetUnitOfWork>();

        private String _connectionString;

        public String ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        #endregion

        #region constructor

        public SqlDAO() { ;}
        /// <summary>
        /// Default constructor with IConnectionFactory
        /// </summary>
        /// <param name="connectionFactory"></param>
        public SqlDAO(IConnectionFactory connectionFactory, [Optional] System.Type type)
        {
            _connectionFactory = connectionFactory;
            _connection = _connectionFactory.Create();
            if (type != null)
            {
                _mapperType = type;
            }
            else
            {
                _mapperType = typeof(GenericMapper);
            }
        }

        /// <summary>
        /// Constructor with connectionString
        /// </summary>
        /// <param name="connectionString">connectionString</param>
        /// <param name="mapperType"></param>
        public SqlDAO(String connectionString, [Optional] System.Type mapperType)
        {
            this._connectionString = connectionString;
            _connection = new SqlConnection(connectionString);
            if (mapperType != null)
            {
                _mapperType = mapperType;
            }
            else
            {
                _mapperType = typeof(GenericMapper);
            }
        }
        #endregion

        #region unitOfWork
        public IUnitOfWork CreateUnitOfWork()
        {
            var transaction = _connection.BeginTransaction();
            var uow = new AdoNetUnitOfWork(transaction, RemoveTransaction, RemoveTransaction);

            _rwLock.EnterWriteLock();
            _uows.AddLast(uow);
            _rwLock.ExitWriteLock();

            return uow;
        }

        private void RemoveTransaction(AdoNetUnitOfWork obj)
        {
            _rwLock.EnterWriteLock();
            _uows.Remove(obj);
            _rwLock.ExitWriteLock();
        }
        #endregion

        #region Sql Commands
        /// <summary>
        /// GetDbSqlCommand 
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public SqlCommand GetSqlQueryCommand(string sqlQuery)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = _connection;
            command.CommandType = CommandType.Text;
            command.CommandText = sqlQuery;
            return command;
        }
        /// <summary>
        /// GetDbSprocCommand 
        /// </summary>
        /// <param name="sprocName"></param>
        /// <returns></returns>
        public SqlCommand GetSprocCommand(string sprocName)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = _connection;
            command.CommandText = sprocName;
            command.CommandType = CommandType.StoredProcedure;
            return command;
        }
        #endregion

        #region Create Parameters
        /// <summary>
        /// Create Null Parameter
        /// </summary>
        /// <param name="name"></param>
        /// <param name="paramType"></param>
        /// <returns></returns>
        public SqlParameter CreateNullParameter(string name, SqlDbType paramType)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.SqlDbType = paramType;
            parameter.ParameterName = name;
            parameter.Value = DBNull.Value;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }
        /// <summary>
        /// CreateNullParameter - with size for nvarchars 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="paramType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public SqlParameter CreateNullParameter(string name, SqlDbType paramType, int size)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.SqlDbType = paramType;
            parameter.ParameterName = name;
            parameter.Size = size;
            parameter.Value = DBNull.Value;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }
        /// <summary>
        /// Create Output Parameter
        /// </summary>
        /// <param name="name"></param>
        /// <param name="paramType"></param>
        /// <returns></returns>
        public SqlParameter CreateOutputParameter(string name, SqlDbType paramType)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.SqlDbType = paramType;
            parameter.ParameterName = name;
            parameter.Direction = ParameterDirection.Output;
            return parameter;
        }
        /// <summary>
        /// CreateOuputParameter - with size for nvarchars 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="paramType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public SqlParameter CreateOutputParameter(string name, SqlDbType paramType, int size)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.SqlDbType = paramType;
            parameter.Size = size;
            parameter.ParameterName = name;
            parameter.Direction = ParameterDirection.Output;
            return parameter;
        }
        /// <summary>
        /// CreateParameter - uniqueidentifier 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlParameter CreateParameter(string name, Guid value)
        {
            if (value.Equals(NullValues.Default_Guid))
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.UniqueIdentifier);
            }
            else
            {
                SqlParameter parameter = new SqlParameter();
                parameter.SqlDbType = SqlDbType.UniqueIdentifier;
                parameter.ParameterName = name;
                parameter.Value = value;
                parameter.Direction = ParameterDirection.Input;
                return parameter;
            }
        }
        /// <summary>
        /// CreateParameter - int 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlParameter CreateParameter(string name, int value)
        {
            if (value == NullValues.Default_Int)
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.Int);
            }
            else
            {
                SqlParameter parameter = new SqlParameter();
                parameter.SqlDbType = SqlDbType.Int;
                parameter.ParameterName = name;
                parameter.Value = value;
                parameter.Direction = ParameterDirection.Input;
                return parameter;
            }
        }
        //phunx1, 20150627: tao ham cho truong hop int?
        public SqlParameter CreateParameter(string name, int? value)
        {
            if (value == null)
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.Int);
            }
            else
            {
                SqlParameter parameter = new SqlParameter();
                parameter.SqlDbType = SqlDbType.Int;
                parameter.ParameterName = name;
                parameter.Value = value;
                parameter.Direction = ParameterDirection.Input;
                return parameter;
            }
        }

        /// <summary>
        /// CreateParameter - datetime 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlParameter CreateParameter(string name, DateTime value)
        {
            if (value == NullValues.Default_DateTime)
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.DateTime);
            }
            else
            {
                SqlParameter parameter = new SqlParameter();
                parameter.SqlDbType = SqlDbType.DateTime;
                parameter.ParameterName = name;
                parameter.Value = value;
                parameter.Direction = ParameterDirection.Input;
                return parameter;
            }
        }
        /// <summary>
        /// CreateParameter - nvarchar 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public SqlParameter CreateParameter(string name, string value, int size)
        {
            if (String.IsNullOrEmpty(value))
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.NVarChar);
            }
            else
            {
                SqlParameter parameter = new SqlParameter();
                parameter.SqlDbType = SqlDbType.NVarChar;
                parameter.Size = size;
                parameter.ParameterName = name;
                parameter.Value = value;
                parameter.Direction = ParameterDirection.Input;
                return parameter;
            }
        }
        /// <summary>
        /// CreateParameter - nvarchar 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public SqlParameter CreateParameter(string name, string value)
        {
            if (String.IsNullOrEmpty(value))
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.NVarChar);
            }
            else
            {
                SqlParameter parameter = new SqlParameter();
                parameter.SqlDbType = SqlDbType.NVarChar;
                parameter.ParameterName = name;
                parameter.Value = value;
                parameter.Direction = ParameterDirection.Input;
                return parameter;
            }
        }
        public SqlParameter CreateParameter(string name, object value, SqlDbType dbType)
        {
            if (value == null)
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.NVarChar);
            }
            else
            {
                SqlParameter parameter = new SqlParameter();
                parameter.SqlDbType = dbType;
                parameter.ParameterName = name;
                parameter.Value = value;
                parameter.Direction = ParameterDirection.Input;
                return parameter;
            }
        }
        /// <summary>
        /// Create parameter - bool
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlParameter CreateParameter(string name, bool value)
        {
            SqlParameter parameter = new SqlParameter();
            parameter.SqlDbType = SqlDbType.Bit;
            parameter.ParameterName = name;
            parameter.Value = value;
            parameter.Direction = ParameterDirection.Input;
            return parameter;
        }
        /// <summary>
        /// CreateParameter - long 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public SqlParameter CreateParameter(string name, long value)
        {
            if (value == NullValues.Default_Long)
            {
                // If value is null then create a null parameter
                return CreateNullParameter(name, SqlDbType.BigInt);
            }
            else
            {
                SqlParameter parameter = new SqlParameter();
                parameter.SqlDbType = SqlDbType.BigInt;
                parameter.ParameterName = name;
                parameter.Value = value;
                parameter.Direction = ParameterDirection.Input;
                return parameter;
            }
        }
        #endregion

        #region Data Projection Methods
        /// <summary>
        /// ExecuteNonQuery 
        /// </summary>
        /// <param name="command"></param>
        public int ExecuteNonQuery(SqlCommand command)
        {
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();

                return command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                if (e is SqlException)
                {
                    throw new Exception(e.ToString());
                }
                else
                {
                    throw new Exception("Error executing query", e);
                }
            }
            finally
            {
                command.Connection.Close();
            }
        }
        /// <summary>
        /// ExecuteScalar
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public Object ExecuteScalar(SqlCommand command)
        {
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();

                return command.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw new Exception("Error executing query", e);
            }
            finally
            {
                command.Connection.Close();
            }
        }

        public int ExecuteQuery(SqlCommand cmd)
        {
            int result = 0;
            try
            {
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                result = cmd.ExecuteNonQuery();
                //result = 1;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                cmd.Connection.Close();
            }
            return result;
        }
        #endregion

        #region Database Helper Methods
        /// <summary>
        /// GetSingleValue 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <returns></returns>
        public T GetSingleValue<T>(SqlCommand command)
        {
            T returnValue = default(T);
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    if (!reader.IsDBNull(0)) { returnValue = (T)reader[0]; }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new DALException("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
            }
            return returnValue;
        }
        /// <summary>
        /// GetSingleString 
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public Int32 GetSingleInt32(SqlCommand command)
        {
            Int32 returnValue = default(int);
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    if (!reader.IsDBNull(0)) { returnValue = reader.GetInt32(0); }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new DALException("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
            }
            return returnValue;
        }

        public Int32 GetSingleInt16(SqlCommand command)
        {
            Int32 returnValue = default(int);
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    if (!reader.IsDBNull(0)) { returnValue = reader.GetInt16(0); }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new DALException("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
            }
            return returnValue;
        }

        /// <summary>
        /// GetSingleString 
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public string GetSingleString(SqlCommand command)
        {
            string returnValue = null;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    if (!reader.IsDBNull(0)) { returnValue = reader.GetString(0); }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new DALException("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
            }
            return returnValue;
        }
        /// <summary>
        /// GetStringList 
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public List<string> GetStringList(SqlCommand command)
        {
            List<string> returnList = null;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    returnList = new List<string>();
                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(0)) { returnList.Add(reader.GetString(0)); }
                    }
                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw new DALException("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
            }
            return returnList;
        }
        #endregion

        #region customMapping



        /// <summary>
        /// GetDynamicDataPage
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataPage<T> GetDataPage<T>(SqlCommand command, int pageIndex, int pageSize,
            Dictionary<String, String> mapDictionary) where T : class
        {
            DataPage<T> page = new DataPage<T>();
            page.PageIndex = pageIndex;
            page.PageSize = pageSize;
            page.Data = new List<T>();
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();


                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    GenericMapper mapper = new GenericMapper(typeof(T));
                    mapper.Map_Dictionary = mapDictionary;
                    mapper.ModelType = typeof(T);

                    while (reader.Read())
                    {
                        // get the data for this row
                        T dto = (T)mapper.GetData(reader);
                        page.Data.Add(dto);
                        page.RecordCount++;
                        //// If we haven't set the RecordCount yet then set it
                        //if (page.RecordCount == 0) { page.RecordCount = mapper.GetRecordCount(reader); }
                    }

                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw e;// new DALException("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
                command.Connection.Dispose();
            }
            return page;
        }
        public T GetSingle<T>(SqlCommand command, Dictionary<String, String> mapDictionary) where T : class
        {
            T dto = null;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                GenericMapper mapper = new GenericMapper(typeof(T));
                mapper.Map_Dictionary = mapDictionary;
                mapper.ModelType = typeof(T);

                if (reader.HasRows)
                {
                    reader.Read();
                    dto = (T)mapper.GetData(reader);
                    reader.Close();
                }

                //IDataMapper mapper = new DataMapperFactory().GetMapper(typeof(T));
                //mapper.Map_Dictionary = mapsDictionary;
                //mapper.ModelType = typeof(T);
                //if (reader.HasRows)
                //{
                //    reader.Read();
                //    dto = (T)mapper.GetData(reader);
                //    reader.Close();
                //}
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                command.Connection.Close();
                command.Connection.Dispose();
            }
            // return the DTO, it's either populated with data or null.
            return dto;
        }
        /// <summary>
        /// nguoi tao: tribh
        /// ngay tao: 22/06/2015
        /// muc dich: lay danh sach theo DataPage
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public DataPage<T> GetDataPage<T>(SqlCommand command, int pageIndex, int pageSize) where T : new()
        {
            try
            {
                DataPage<T> lstPage = new DataPage<T>();
                lstPage.PageIndex = pageIndex;
                lstPage.PageSize = pageSize;
                lstPage.Data = GetList<T>(command);
                return lstPage;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// nguoi tao: TriBH
        /// Ngay tao: 27.05.2015
        /// Muc dich: Mapping va chay lenh tu store va object
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <returns></returns>
        public T GetSingle<T>(SqlCommand command) where T : new()
        {
            T dto = new T();
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();                    
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        if (reader.GetValue(i) != DBNull.Value)
                        {
                            PropertyInfo propertyInfo = typeof(T).GetProperty(reader.GetName(i));
                            if (propertyInfo != null)
                            {
                                propertyInfo.SetValue(dto, reader.GetValue(i), null);
                            }
                        }
                    }
                    reader.Close();
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                command.Connection.Close();
                command.Connection.Dispose();
            }
            
            return dto;
        }

        /// <summary>
        /// nguoi tao: tribh
        /// ngay tao: 27.05.2015
        /// muc dich: mapping get, list
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <returns></returns>
        public List<T> GetList<T>(SqlCommand command) where T : new()
        {
            List<T> dtoList = new List<T>();
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        T tempObject = new T();

                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            if (reader.GetValue(i) != DBNull.Value)
                            {
                                PropertyInfo propertyInfo = typeof(T).GetProperty(reader.GetName(i));
                                if (propertyInfo != null)
                                {
                                    propertyInfo.SetValue(tempObject, reader.GetValue(i), null);
                                }
                            }
                        }

                        dtoList.Add(tempObject);
                    }

                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw e;// new DALException("Error populating data", e);
            }
            finally
            {
                command.Connection.Close();
            }
            // We return either the populated list if there was data,
            // or if there was no data we return an empty list.
            return dtoList;
        }

        /// <summary>
        /// GetList
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="command"></param>
        /// <returns></returns>
        public List<T> GetList<T>(SqlCommand command, Dictionary<String, String> mapDictionary) where T : class
        {
            List<T> dtoList = new List<T>();
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                    command.Connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    GenericMapper mapper = new GenericMapper(typeof(T));
                    mapper.Map_Dictionary = mapDictionary;
                    mapper.ModelType = typeof(T);

                    while (reader.Read())
                    {
                        T dto = (T)mapper.GetData(reader);
                        dtoList.Add(dto);
                    }

                    reader.Close();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                command.Connection.Close();
            }
            // We return either the populated list if there was data,
            // or if there was no data we return an empty list.
            return dtoList;
        }

        public string GetParameterValue(string original, bool compareEqual)
        {
            string result = original;
            if (!compareEqual)
            {
                result = "%" + original + "%";
            }

            return result;
        }
        #endregion

        /// <summary>
        /// Bảng danh sách các id
        /// </summary>
        /// <param name="listId">Mảng các id</param>
        /// <returns></returns>
        public DataTable GetListID(string[] listId)
        {
            DataTable tableId = new DataTable("tableId");
            tableId.Columns.Add("ID");
            int len = listId.Length;
            for(int i = 0; i< len;i++)
            {
                tableId.Rows.Add(listId[i]);
            }

            return tableId;
        }

        protected DataTable GetTableId<T>(T[] listId)
        {
            DataTable table = GetTableId<T>(listId, "Id");

            return table;
        }

        protected DataTable GetTableId<T>(T[] listId, string fieldName)
        {
            DataTable table = new DataTable();
            table.Columns.Add(fieldName);

            int len = listId.Length;
            for (int i = 0; i < len; i++)
            {
                table.Rows.Add(listId[i]);
            }

            return table;
        }
    }


}
